using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Collections;
using System.Drawing.Printing;

using System.Drawing;
using System.Net.Mail;
using System.IO;
using System.Data.SqlClient;
using ExtendedDataTableLibrary.Properties;

namespace ExtendedDataTableLibrary
{
    public class ExtendedDataTable : DataTable
    {
        #region members
        private int lastRowPrinted = -1;
        private Hashtable columnWidths = new Hashtable();
        private Hashtable rowheights = new Hashtable();
        private StyleProperties _StyleProperties = new StyleProperties();
        private FormatProperties _FormatProperties = new FormatProperties();
        private PrintProperties _PrintProperties = new PrintProperties();
        private MailProperties _MailProperties = new MailProperties();
        private DataProperties _DataProperties = new DataProperties();
        #endregion
        #region ctors
        public ExtendedDataTable()
            : base()
        {

        }
        public ExtendedDataTable(DataTable dt)
            : base()
        {

            ImportDataTable(dt);
        }
        #endregion
        #region properties
        public StyleProperties StyleProperties
        {
            get { return _StyleProperties; }
        }
        public FormatProperties FormatProperties
        {
            get { return _FormatProperties; }
        }
        public PrintProperties PrintProperties
        {
            get { return _PrintProperties; }
        }
        public MailProperties MailProperties
        {
            get { return _MailProperties; }
        }
        public DataProperties DataProperties
        {
            get { return _DataProperties; }
        }
        #endregion

        public void ImportDataTable(DataTable dt)
        {
            this.Columns.Clear();
            this.Rows.Clear();
            foreach (DataColumn dc in dt.Columns)
            {
                this.Columns.Add(dc.ColumnName, dc.DataType);
            }
            foreach (DataRow dr in dt.Rows)
            {
                this.Rows.Add(dr.ItemArray);
            }
        }

        #region ConvertToHtml
        public string ToHTML()
        {
            try
            {
                int columnCount = 0;
                StringBuilder sb = new StringBuilder();
                sb.Append(@"<table style=""");
                sb.Append(StyleProperties.TableStyle.Value);
                sb.Append(@""">");
                sb.Append(@"<tr style=""");
                sb.Append(StyleProperties.HeaderStyle.Value);
                sb.Append(@""">");
                foreach (DataColumn dc in this.Columns)
                {
                    if (FormatProperties.ColumnNames.Keys.Count == 0 || FormatProperties.ColumnNames.ContainsKey(dc.ColumnName))
                    {
                        sb.Append("<td align=center>");
                        if (FormatProperties.ColumnNames.Keys.Count == 0)
                        {
                            sb.Append(dc.ToString());
                        }
                        else if (FormatProperties.ColumnNames.ContainsKey(dc.ColumnName))
                        {
                            sb.Append(FormatProperties.ColumnNames[dc.ColumnName].ToString());
                        }
                        sb.Append("</td>");
                        columnCount++;
                    }

                }
                sb.Append("</tr>");
                if (this.Rows.Count > 0)
                {
                    for (int i = 0; i < this.Rows.Count; i++)
                    {
                        sb.Append(@"<tr style=""");
                        if (StyleProperties.RowSpecificStyle.ContainsKey(i))
                        {
                            sb.Append(StyleProperties.RowSpecificStyle[i].Value);
                        }
                        else
                        {
                            if (i % 2 == 0)
                            {
                                sb.Append(StyleProperties.RowStyle.Value);
                            }
                            else
                            {
                                sb.Append(StyleProperties.AlternatingRowStyle.Value);
                            }
                        }
                        sb.Append(@""">");
                        foreach (DataColumn dc in this.Columns)
                        {
                            if (FormatProperties.ColumnNames.Keys.Count == 0 || FormatProperties.ColumnNames.ContainsKey(dc.ColumnName))
                            {
                                sb.Append("<td");
                                if (FormatProperties.ColumnAlignments.ContainsKey(dc.ColumnName))
                                {
                                    sb.Append(" align=");
                                    sb.Append(FormatProperties.ColumnAlignments[dc.ColumnName].ToString());
                                }

                                sb.Append(">");
                                if (FormatProperties.ColumnFormats.ContainsKey(dc.ColumnName))
                                {
                                    sb.Append(String.Format(FormatProperties.ColumnFormats[dc.ColumnName].ToString(), this.Rows[i][dc]));
                                }
                                else
                                {
                                    sb.Append(this.Rows[i][dc].ToString());
                                }
                                sb.Append("</td>");
                            }
                        }
                        sb.Append("</tr>");
                    }
                }
                else
                {
                    sb.Append("<tr><td colspan=" + columnCount.ToString() + ">No Records</td></tr>");
                }

                sb.Append("</table>");

                return sb.ToString();
            }
            catch (Exception ex)
            {
                throw new Exception("Exception Occured While Generating HTML", ex);
            }


        }
        #endregion
        #region Print
        public void Print()
        {
            if (System.Web.HttpContext.Current == null)
            {
                PrintDocument p = PrintProperties.PrintDocument;
                p.PrintPage += new PrintPageEventHandler(p_PrintPage);
                try
                {
                    p.Print();
                }
                catch (Exception ex)
                {
                    throw new Exception("Exception Occured While Printing", ex);
                }

            }
            else
            {
                string s = this.ToHTML();
                s += "<script language='javascript'>window.print()</script>";
                System.Web.HttpContext.Current.Response.Clear();
                System.Web.HttpContext.Current.Response.Write(s);
            }
        }
        private Color GetColor(string s)
        {
            if (s.Length == 6)
            {
                try
                {
                    int r = ConvertToInt(s.Substring(0, 2));
                    int g = ConvertToInt(s.Substring(2, 2));
                    int b = ConvertToInt(s.Substring(4, 2));
                    return Color.FromArgb(r, g, b);
                }
                catch (Exception)
                {

                    return Color.Black;
                }

            }
            else
            {
                return Color.Black;
            }
        }
        private int ConvertToInt(string s)
        {
            int retval = 0;
            switch (s.Substring(0, 1).ToLower())
            {
                case "a":
                    retval += (10 * 15);
                    break;
                case "b":
                    retval += (11 * 15);
                    break;
                case "c":
                    retval += (12 * 15);
                    break;
                case "d":
                    retval += (13 * 15);
                    break;
                case "e":
                    retval += (14 * 15);
                    break;
                case "f":
                    retval += (15 * 15);
                    break;
                default:
                    retval += (15 * Convert.ToInt32(s.Substring(0, 1)));
                    break;
            }

            switch (s.Substring(1, 1).ToLower())
            {
                case "a":
                    retval += (10);
                    break;
                case "b":
                    retval += (11);
                    break;
                case "c":
                    retval += (12);
                    break;
                case "d":
                    retval += (13);
                    break;
                case "e":
                    retval += (14);
                    break;
                case "f":
                    retval += (15);
                    break;
                default:
                    retval += (Convert.ToInt32(s.Substring(1, 1)));
                    break;
            }
            return retval;
        }
        void p_PrintPage(object sender, PrintPageEventArgs e)
        {

            int pageHeight = e.PageBounds.Height - 20;
            if (columnWidths.Count == 0)
            {
                int totalWidth = 0;
                foreach (DataColumn dc in this.Columns)
                {
                    string header = "";
                    if (FormatProperties.ColumnNames[dc.ColumnName] == null)
                    {
                        header = dc.ColumnName;
                    }
                    else
                    {
                        header = FormatProperties.ColumnNames[dc.ColumnName].ToString();
                    }
                    int width = Convert.ToInt32(e.Graphics.MeasureString(header, PrintProperties.Font).Width);
                    foreach (DataRow dr in this.Rows)
                    {
                        int dataWidth = Convert.ToInt32(e.Graphics.MeasureString(dr[dc].ToString(), PrintProperties.Font).Width);
                        if (dataWidth > width)
                        {
                            width = dataWidth;
                        }
                    }

                    width += 5;
                    columnWidths.Add(dc.ColumnName, width);
                    totalWidth += width;
                }

                int pageWidth = e.PageBounds.Width - 20;
                decimal ratio = 1;
                if (totalWidth > pageWidth)
                {
                    ratio = Convert.ToDecimal(pageWidth) / Convert.ToDecimal(totalWidth);
                    Hashtable cwidthsTemp = new Hashtable();
                    foreach (DictionaryEntry de in columnWidths)
                    {
                        cwidthsTemp.Add(de.Key, Convert.ToInt32(Math.Floor(Convert.ToDecimal(de.Value) * ratio)));
                    }
                    columnWidths = cwidthsTemp;
                }

            }
            if (rowheights.Count == 0)
            {
                foreach (DataRow dr in this.Rows)
                {
                    int height = 0;
                    foreach (DataColumn dc in this.Columns)
                    {
                        int dataHeight = Convert.ToInt32(e.Graphics.MeasureString(dr[dc].ToString(), PrintProperties.Font, Convert.ToInt32(columnWidths[dc.ColumnName])).Height);
                        if (dataHeight > height)
                        {
                            height = dataHeight;
                        }
                    }
                    rowheights.Add(this.Rows.IndexOf(dr), height);
                }
                int headerHeight = 0;
                foreach (DataColumn dc in this.Columns)
                {
                    string header = "";
                    if (FormatProperties.ColumnNames[dc.ColumnName] == null)
                    {
                        header = dc.ColumnName;
                    }
                    else
                    {
                        header = FormatProperties.ColumnNames[dc.ColumnName].ToString();
                    }
                    int dataHeaderHeight = Convert.ToInt32(e.Graphics.MeasureString(header, PrintProperties.Font, Convert.ToInt32(columnWidths[dc.ColumnName])).Height);
                    if (dataHeaderHeight > headerHeight)
                    {
                        headerHeight = dataHeaderHeight;
                    }
                }
                rowheights.Add(-1, headerHeight);
            }

            foreach (DataColumn dc in this.Columns)
            {
                int x = 10;
                for (int i = this.Columns.IndexOf(dc) - 1; i >= 0; i--)
                {
                    x += Convert.ToInt32(columnWidths[this.Columns[i].ColumnName]);
                }
                int y = 10;
                int width = Convert.ToInt32(columnWidths[dc.ColumnName]);
                int height = Convert.ToInt32(rowheights[-1]);
                Rectangle r = new Rectangle(x, y, width, height);
                Rectangle rIn = new Rectangle(x + 1, y + 1, width - 2, height - 2);
                e.Graphics.DrawRectangle(PrintProperties.TableBorderPen, r);
                string headerBgColor = "";
                if (StyleProperties.HeaderStyle.ContainsKey("background-color"))
                {
                    headerBgColor = StyleProperties.HeaderStyle["background-color"].Replace("#", "");
                }
                string headerForeColor = "";
                if (StyleProperties.HeaderStyle.ContainsKey("color"))
                {
                    headerForeColor = StyleProperties.HeaderStyle["color"].Replace("#", "");
                }
                e.Graphics.FillRectangle(new SolidBrush(GetColor(headerBgColor)), rIn);
                string header = "";
                if (FormatProperties.ColumnNames[dc.ColumnName] == null)
                {
                    header = dc.ColumnName;
                }
                else
                {
                    header = FormatProperties.ColumnNames[dc.ColumnName].ToString();
                }
                e.Graphics.DrawString(header, PrintProperties.Font, new SolidBrush(GetColor(headerForeColor)), rIn);
            }
            foreach (DataRow dr in this.Rows)
            {
                if (this.Rows.IndexOf(dr) > lastRowPrinted)
                {
                    foreach (DataColumn dc in this.Columns)
                    {
                        int x = 10;
                        for (int i = this.Columns.IndexOf(dc) - 1; i >= 0; i--)
                        {
                            x += Convert.ToInt32(columnWidths[this.Columns[i].ColumnName]);
                        }
                        int y = 10 + Convert.ToInt32(rowheights[-1]);
                        for (int i = this.Rows.IndexOf(dr) - 1; i > lastRowPrinted; i--)
                        {
                            y += Convert.ToInt32(rowheights[i]);
                        }
                        int width = Convert.ToInt32(columnWidths[dc.ColumnName]);
                        int height = Convert.ToInt32(rowheights[this.Rows.IndexOf(dr)]);
                        if (height + y > pageHeight)
                        {
                            e.HasMorePages = true;
                            lastRowPrinted = this.Rows.IndexOf(dr) - 1;
                            return;
                        }
                        string rowBgColor = "";
                        if (StyleProperties.RowStyle.ContainsKey("background-color"))
                        {
                            rowBgColor = StyleProperties.RowStyle["background-color"].Replace("#", "");
                        }
                        string rowForeColor = ""; ;
                        if (StyleProperties.RowStyle.ContainsKey("color"))
                        {
                            rowForeColor = StyleProperties.RowStyle["color"].Replace("#", "");
                        }
                        string altRowBgColor = "";
                        if (StyleProperties.AlternatingRowStyle.ContainsKey("background-color"))
                        {
                            altRowBgColor = StyleProperties.AlternatingRowStyle["background-color"].Replace("#", "");
                        }
                        string altRowForeColor = "";
                        if (StyleProperties.AlternatingRowStyle.ContainsKey("color"))
                        {
                            altRowForeColor = StyleProperties.AlternatingRowStyle["color"].Replace("#", "");
                        }


                        Rectangle r = new Rectangle(x, y, width, height);
                        Rectangle rIn = new Rectangle(x + 1, y + 1, width - 2, height - 2);

                        if (StyleProperties.RowSpecificStyle.ContainsKey(this.Rows.IndexOf(dr)))
                        {
                            string specialBgColor = "";
                            if (StyleProperties.RowSpecificStyle[this.Rows.IndexOf(dr)].ContainsKey("background-color"))
                            {
                                specialBgColor = StyleProperties.RowSpecificStyle[this.Rows.IndexOf(dr)]["background-color"].Replace("#", "");
                            }
                            string specialForeColor = "";
                            if (StyleProperties.RowSpecificStyle[this.Rows.IndexOf(dr)].ContainsKey("color"))
                            {
                                specialForeColor = StyleProperties.RowSpecificStyle[this.Rows.IndexOf(dr)]["color"].Replace("#", "");
                            }

                            e.Graphics.DrawRectangle(PrintProperties.TableBorderPen, r);
                            e.Graphics.FillRectangle(new SolidBrush(GetColor(specialBgColor)), rIn);
                            e.Graphics.DrawString(dr[dc].ToString(), PrintProperties.Font, new SolidBrush(GetColor(specialForeColor)), rIn);
                        }
                        else
                        {
                            if (this.Rows.IndexOf(dr) % 2 == 0)
                            {
                                e.Graphics.DrawRectangle(PrintProperties.TableBorderPen, r);
                                e.Graphics.FillRectangle(new SolidBrush(GetColor(rowBgColor)), rIn);
                                e.Graphics.DrawString(dr[dc].ToString(), PrintProperties.Font, new SolidBrush(GetColor(rowForeColor)), rIn);
                            }
                            else
                            {
                                e.Graphics.DrawRectangle(PrintProperties.TableBorderPen, r);
                                e.Graphics.FillRectangle(new SolidBrush(GetColor(altRowBgColor)), rIn);
                                e.Graphics.DrawString(dr[dc].ToString(), PrintProperties.Font, new SolidBrush(GetColor(altRowForeColor)), rIn);
                            }
                        }
                    }
                }
            }


        }
        #endregion
        #region SendMail
        public void SendMail(string from, string to, string subject)
        {
            MailMessage mm = new MailMessage(from, to, subject, this.ToHTML());
            mm.IsBodyHtml = true;
            try
            {
                MailProperties.SmtpClient.Send(mm);
            }
            catch (Exception ex)
            {
                throw new Exception("Exception Occured While Sending Email", ex);
            }
        }
        #endregion
        #region ConvertToWord
        public void ToWord(string fileName)
        {
            Microsoft.Office.Interop.Word.Application insApplication = new Microsoft.Office.Interop.Word.Application();
            System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            object missing = System.Reflection.Missing.Value;
            object FileName = fileName;
            object SaveAs = false;
            try
            {
                
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                Microsoft.Office.Interop.Word.Document insDocument;
                insApplication.Visible = false;

                object Template = Type.Missing;
                object newTemplate = false;
                object DocumentType = Microsoft.Office.Interop.Word.WdDocumentType.wdTypeDocument;
                object Visible = false;

                insDocument = insApplication.Documents.Add(ref Template, ref newTemplate, ref DocumentType, ref Visible);


                Microsoft.Office.Interop.Word.Range r;
                object start = 0;
                object end = 0;
                r = insDocument.Range(ref start, ref end);

                object Autofit = Microsoft.Office.Interop.Word.WdAutoFitBehavior.wdAutoFitContent;
                object TableBehaviour = Microsoft.Office.Interop.Word.WdDefaultTableBehavior.wdWord9TableBehavior;
                Microsoft.Office.Interop.Word.Table insTable = insDocument.Tables.Add(r, this.Rows.Count + 1, this.Columns.Count, ref TableBehaviour, ref Autofit);

                for (int i = 0; i < this.Columns.Count; i++)
                {
                    if (FormatProperties.ColumnNames.Keys.Count == 0 || FormatProperties.ColumnNames.ContainsKey(this.Columns[i].ColumnName))
                    {
                        insTable.Cell(1, i + 1).Range.Text = FormatProperties.ColumnNames[this.Columns[i].ColumnName].ToString();
                    }
                    else
                    {
                        insTable.Cell(1, i + 1).Range.Text = this.Columns[i].ColumnName;
                    }
                }
                for (int i = 0; i < this.Rows.Count; i++)
                {
                    for (int j = 0; j < this.Columns.Count; j++)
                    {
                        insTable.Cell(i + 2, j + 1).Range.Text = this.Rows[i][j].ToString();
                    }
                }
              

                insDocument.SaveAs(ref FileName, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing);

                
                insApplication.Quit(ref SaveAs, ref missing, ref missing);
                System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
            }
            catch (Exception ex)
            {
                insApplication.Quit(ref SaveAs, ref missing, ref missing);
                System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
                throw new Exception("Exception Occured While Generating Word Document", ex);
            }
        }
        #endregion
        #region ConvertToExcel
        public void ToExcel(string fileName)
        {
            Microsoft.Office.Interop.Excel.Application insApplication = new Microsoft.Office.Interop.Excel.Application();

            System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            try
            {

                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                Microsoft.Office.Interop.Excel.Workbook insWorkBook;
                insApplication.Visible = false;

                insWorkBook = insApplication.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                Microsoft.Office.Interop.Excel.Worksheet insWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)insWorkBook.Worksheets[1];
                insWorkSheet.Rows.Delete(false);
                insWorkSheet.Columns.Delete(false);

                for (int i = 0; i < this.Columns.Count; i++)
                {
                    if (FormatProperties.ColumnNames.Keys.Count == 0 || FormatProperties.ColumnNames.ContainsKey(this.Columns[i].ColumnName))
                    {
                        insWorkSheet.Cells[1, i + 1] = FormatProperties.ColumnNames[this.Columns[i].ColumnName].ToString();
                    }
                    else
                    {
                        insWorkSheet.Cells[1, i + 1] = this.Columns[i].ColumnName;
                    }
                }
                for (int i = 0; i < this.Rows.Count; i++)
                {
                    for (int j = 0; j < this.Columns.Count; j++)
                    {
                        insWorkSheet.Cells[i + 2, j + 1] = this.Rows[i][j].ToString();
                    }
                }
                insWorkSheet.Columns.AutoFit();
                insWorkBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, null, null, null, null, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

                insApplication.Quit();
                System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
            }
            catch (Exception ex)
            {
                insApplication.Quit();
                System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
                throw new Exception("Exception Occured While Generating Excel Document", ex);
            }

        }
        #endregion
        #region Data

        public void Fill()
        {
            DataProperties.Command.Connection = DataProperties.Connection;
            DataProperties.Adapter.SelectCommand = DataProperties.Command;
            DataProperties.Adapter.UpdateCommand = new SqlCommandBuilder(DataProperties.Adapter).GetUpdateCommand();
            DataProperties.Adapter.DeleteCommand = new SqlCommandBuilder(DataProperties.Adapter).GetDeleteCommand();
            DataProperties.Adapter.InsertCommand = new SqlCommandBuilder(DataProperties.Adapter).GetInsertCommand();
            try
            {
                DataProperties.Adapter.Fill(this);
            }
            catch (Exception ex)
            {
                throw new Exception("Exception Occured While Filling DataTable", ex);
            }

        }
        public void Update()
        {
            try
            {
                DataProperties.Adapter.Update(this);
            }
            catch (Exception ex)
            {
                throw new Exception("Exception Occured While Updating DataTable", ex);
            }
        }
        #endregion
    }
}
